- Notifications
You must be signed in to change notification settings - Fork 849
/
Copy pathOML4SQL Classification GLM.dsnb
executable file
·1 lines (1 loc) · 63.2 KB
/
OML4SQL Classification GLM.dsnb
1
[{"layout":null,"template":null,"templateConfig":null,"name":"OML4SQL Classification GLM","description":null,"readOnly":false,"type":"medium","paragraphs":[{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":null,"title":null,"message":[],"enabled":true,"result":{"startTime":1715229629998,"interpreter":"md.medium","endTime":1715229630151,"results":[],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":true,"width":0,"hideResult":true,"dynamicFormParams":null,"row":0,"hasTitle":false,"hideVizConfig":true,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"html","title":null,"message":["%md","# Classification Modeling to Predict Target Customers using Generalized Linear Model","","In this notebook, we predict customers most likely to be positive responders to an Affinity Card loyalty program. High Affinity Card responders (target value = 1) are defined as those customers who when given a loyaly or affinity card hyper-respond i.e. they increae their purchasing higher than the Affinity Card program's offered discount percentage. This notebook builds and applies classification generalized linear models using the SH schema data. All processing occurs inside Oracle Autonomous Database.","","Copyright (c) 2024 Oracle Corporation ","###### <a href=\"https://oss.oracle.com/licenses/upl/\" onclick=\"return ! window.open('https://oss.oracle.com/licenses/upl/');\">The Universal Permissive License (UPL), Version 1.0<\/a>","---"],"enabled":true,"result":{"startTime":1715229630227,"interpreter":"md.medium","endTime":1715229630321,"results":[{"message":"<h1 id=\"classification-modeling-to-predict-target-customers-using-generalized-linear-model\">Classification Modeling to Predict Target Customers using Generalized Linear Model<\/h1>\n<p>In this notebook, we predict customers most likely to be positive responders to an Affinity Card loyalty program. High Affinity Card responders (target value = 1) are defined as those customers who when given a loyaly or affinity card hyper-respond i.e. they increae their purchasing higher than the Affinity Card program's offered discount percentage. This notebook builds and applies classification generalized linear models using the SH schema data. All processing occurs inside Oracle Autonomous Database.<\/p>\n<p>Copyright (c) 2024 Oracle Corporation<\/p>\n<h6 id=\"the-universal-permissive-license-upl-version-10\"><a href=\"https://oss.oracle.com/licenses/upl/\" onclick=\"return ! window.open('https://oss.oracle.com/licenses/upl/');\">The Universal Permissive License (UPL), Version 1.0<\/a><\/h6>\n<hr />\n","type":"HTML"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":true,"width":10,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":false,"hideVizConfig":true,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"html","title":null,"message":["%md","<dl>","<img src=\"http://www.oracle.com/technetwork/database/options/advanced-analytics/classification-5663162.jpg\" alt=\"OML Notebooks\" width=\"250\"/>","<\/dl>"],"enabled":true,"result":{"startTime":1715229647394,"interpreter":"md.medium","endTime":1715229647458,"results":[{"message":"<dl>\n<img src=\"http://www.oracle.com/technetwork/database/options/advanced-analytics/classification-5663162.jpg\" alt=\"OML Notebooks\" width=\"250\"/>\n<\/dl>\n","type":"HTML"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":true,"width":2,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":false,"hideVizConfig":true,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"html","title":"For more information...","message":["%md","* <a href=\"https://docs.oracle.com/en/cloud/paas/autonomous-data-warehouse-cloud/index.html\" target=\"_blank\">Oracle ADB Documentation<\/a>","* <a href=\"https://github.com/oracle/oracle-db-examples/tree/master/machine-learning\" target=\"_blank\">OML folder on Oracle GitHub<\/a>","* <a href=\"https://www.oracle.com/machine-learning\" target=\"_blank\">OML Web Page<\/a>","* <a href=\"https://www.oracle.com/goto/ml-classification\" target=\"_blank\">OML Classification<\/a>","* <a href=\"https://www.oracle.com/goto/ml-generalized-linear-model\" target=\"_blank\">OML Generalized Linear Model<\/a>"],"enabled":true,"result":{"startTime":1715224812906,"interpreter":"md.medium","endTime":1715224812975,"results":[{"message":"<ul>\n<li><a href=\"https://docs.oracle.com/en/cloud/paas/autonomous-data-warehouse-cloud/index.html\" target=\"_blank\">Oracle ADB Documentation<\/a><\/li>\n<li><a href=\"https://github.com/oracle/oracle-db-examples/tree/master/machine-learning\" target=\"_blank\">OML folder on Oracle GitHub<\/a><\/li>\n<li><a href=\"https://www.oracle.com/machine-learning\" target=\"_blank\">OML Web Page<\/a><\/li>\n<li><a href=\"https://www.oracle.com/goto/ml-classification\" target=\"_blank\">OML Classification<\/a><\/li>\n<li><a href=\"https://www.oracle.com/goto/ml-generalized-linear-model\" target=\"_blank\">OML Generalized Linear Model<\/a><\/li>\n<\/ul>\n","type":"HTML"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":true,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":true,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"table","title":"Display the SUPPLEMENTARY_DEMOGRAPHICS data ","message":["%sql","","SELECT * ","FROM SH.SUPPLEMENTARY_DEMOGRAPHICS","FETCH FIRST 10 ROWS ONLY;"],"enabled":true,"result":{"startTime":1715224813177,"interpreter":"sql.medium","endTime":1715224813298,"results":[{"message":"CUST_ID\tEDUCATION\tOCCUPATION\tHOUSEHOLD_SIZE\tYRS_RESIDENCE\tAFFINITY_CARD\tBULK_PACK_DISKETTES\tFLAT_PANEL_MONITOR\tHOME_THEATER_PACKAGE\tBOOKKEEPING_APPLICATION\tPRINTER_SUPPLIES\tY_BOX_GAMES\tOS_DOC_SET_KANJI\tCOMMENTS\n102547\t10th\tOther\t1\t0\t0\t1\t1\t0\t0\t1\t1\t0\t\n101050\t10th\tOther\t1\t0\t0\t1\t1\t0\t0\t1\t1\t0\t\n100040\t11th\tSales\t1\t0\t0\t1\t1\t0\t0\t1\t1\t0\t\n102117\tHS-grad\tFarming\t1\t0\t0\t0\t0\t0\t1\t1\t1\t0\t\n101074\t10th\tHandler\t1\t1\t0\t1\t1\t0\t0\t1\t1\t0\t\n104179\t10th\tHandler\t1\t1\t0\t1\t1\t0\t0\t1\t1\t0\t\n100417\t11th\tHandler\t1\t1\t0\t0\t0\t0\t0\t1\t1\t0\t\n101146\t< Bach.\t?\t1\t1\t0\t1\t1\t0\t1\t1\t1\t0\t\n103420\t< Bach.\t?\t1\t1\t0\t1\t1\t0\t1\t1\t1\t0\t\n101987\t< Bach.\tOther\t1\t1\t0\t1\t1\t0\t1\t1\t1\t0\t\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"raw","title":"Count number of records in SUPPLEMENTARY_DEMOGRAPHICS table","message":["%sql","","SELECT COUNT(*) FROM SH.SUPPLEMENTARY_DEMOGRAPHICS;"],"enabled":true,"result":{"startTime":1715224813375,"interpreter":"sql.medium","endTime":1715224813648,"results":[{"message":"COUNT(*)\n4500\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":4,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":"[{\"table\":{\"version\":1},\"bar\":{\"showSeries\":[\"COUNT\"],\"series\":{\"availableSeriesElements\":[{\"id\":\"COUNT\",\"lineType\":\"straight\",\"borderColor\":\"rgb(25, 95, 116)\",\"borderWidth\":0,\"color\":\"rgb(25, 95, 116)\",\"pattern\":\"auto\",\"markerColor\":\"rgb(25, 95, 116)\",\"markerDisplayed\":\"auto\",\"markerShape\":\"auto\",\"markerSize\":0}]},\"lastColumns\":[\"AFFINITY_CARD\",\"COUNT\"],\"version\":1}}]","hideInIFrame":false,"selectedVisualization":"bar","title":"Show distribution of AFFINITY_CARD responders","message":["%sql","","-- In this data set, hyper-responders to the Affinity Card loyalty program are represented as 1's.","","SELECT AFFINITY_CARD, COUNT(*) COUNT","FROM SH.SUPPLEMENTARY_DEMOGRAPHICS","GROUP BY AFFINITY_CARD;"],"enabled":true,"result":{"startTime":1715224813728,"interpreter":"sql.medium","endTime":1715224813882,"results":[{"message":"AFFINITY_CARD\tCOUNT\n0\t3428\n1\t1072\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":8,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":"[{\"bar\":{\"groupByColumns\":[\"HOUSEHOLD_SIZE\",\"AFFINITY_CARD\"],\"series\":{\"availableSeriesElements\":[{\"id\":\"NUM_CUSTOMERS\",\"lineType\":\"straight\",\"borderColor\":\"rgb(25, 95, 116)\",\"borderWidth\":0,\"color\":\"rgb(25, 95, 116)\",\"pattern\":\"auto\",\"markerColor\":\"rgb(25, 95, 116)\",\"markerDisplayed\":\"auto\",\"markerShape\":\"auto\",\"markerSize\":0}]},\"lastColumns\":[\"NUM_CUSTOMERS\",\"HOUSEHOLD_SIZE\",\"AFFINITY_CARD\"],\"version\":1}}]","hideInIFrame":false,"selectedVisualization":"bar","title":"Graph HOUSEHOLD_SIZE grouped by AFFINITY_CARD responders","message":["%sql","","SELECT COUNT(CUST_ID) AS NUM_CUSTOMERS, HOUSEHOLD_SIZE, AFFINITY_CARD ","FROM SH.SUPPLEMENTARY_DEMOGRAPHICS ","GROUP BY HOUSEHOLD_SIZE, AFFINITY_CARD;"],"enabled":true,"result":{"startTime":1715224813960,"interpreter":"sql.medium","endTime":1715224814080,"results":[{"message":"NUM_CUSTOMERS\tHOUSEHOLD_SIZE\tAFFINITY_CARD\n2\t6-8\t1\n109\t2\t1\n11\t1\t1\n973\t3\t0\n29\t9+\t1\n112\t4-5\t0\n146\t6-8\t0\n476\t9+\t0\n814\t3\t1\n107\t4-5\t1\n681\t1\t0\n1040\t2\t0\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":6,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":"[{\"bar\":{\"showSeries\":[\"AFFINITY_0_COUNT\",\"AFFINITY_1_COUNT\"],\"aggregationOption\":\"Last\",\"series\":{\"availableSeriesElements\":[{\"id\":\"AFFINITY_0_COUNT\",\"lineType\":\"straight\",\"borderColor\":\"rgb(50, 146, 94)\",\"borderWidth\":0,\"color\":\"rgb(50, 146, 94)\",\"pattern\":\"auto\",\"markerColor\":\"rgb(50, 146, 94)\",\"markerDisplayed\":\"auto\",\"markerShape\":\"auto\",\"markerSize\":0},{\"id\":\"AFFINITY_1_COUNT\",\"lineType\":\"straight\",\"borderColor\":\"rgb(50, 146, 94)\",\"borderWidth\":0,\"color\":\"rgb(50, 146, 94)\",\"pattern\":\"auto\",\"markerColor\":\"rgb(50, 146, 94)\",\"markerDisplayed\":\"auto\",\"markerShape\":\"auto\",\"markerSize\":0}]},\"lastColumns\":[\"HOUSEHOLD_SIZE\",\"AFFINITY_1_COUNT\",\"AFFINITY_0_COUNT\"],\"version\":1}}]","hideInIFrame":false,"selectedVisualization":"bar","title":"Graph HOUSEHOLD_SIZE grouped by AFFINITY_CARD responders","message":["%sql","","SELECT HOUSEHOLD_SIZE,"," SUM(CASE WHEN AFFINITY_CARD = 1 THEN NUM_CUSTOMERS ELSE 0 END) AS AFFINITY_1_COUNT,"," SUM(CASE WHEN AFFINITY_CARD = 0 THEN NUM_CUSTOMERS ELSE 0 END) AS AFFINITY_0_COUNT","FROM (SELECT COUNT(CUST_ID) AS NUM_CUSTOMERS, HOUSEHOLD_SIZE, AFFINITY_CARD "," FROM SH.SUPPLEMENTARY_DEMOGRAPHICS "," GROUP BY HOUSEHOLD_SIZE, AFFINITY_CARD)","GROUP BY HOUSEHOLD_SIZE","ORDER BY HOUSEHOLD_SIZE"],"enabled":true,"result":{"startTime":1715224814157,"interpreter":"sql.medium","endTime":1715224814276,"results":[{"message":"HOUSEHOLD_SIZE\tAFFINITY_1_COUNT\tAFFINITY_0_COUNT\n1\t11\t681\n2\t109\t1040\n3\t814\t973\n4-5\t107\t112\n6-8\t2\t146\n9+\t29\t476\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":6,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"raw","title":"Create view DEMOGRAPHICS4_V with desired columns for analysis","message":["%script","","CREATE OR REPLACE VIEW DEMOGRAPHICS4_V ","AS SELECT AFFINITY_CARD, CUST_ID, BOOKKEEPING_APPLICATION,"," BULK_PACK_DISKETTES, EDUCATION,"," FLAT_PANEL_MONITOR, HOME_THEATER_PACKAGE, "," HOUSEHOLD_SIZE, OCCUPATION, OS_DOC_SET_KANJI,"," PRINTER_SUPPLIES, YRS_RESIDENCE, Y_BOX_GAMES"," FROM SH.SUPPLEMENTARY_DEMOGRAPHICS;"," "],"enabled":true,"result":{"startTime":1715224814355,"interpreter":"script.medium","endTime":1715224814790,"results":[{"message":"\nView DEMOGRAPHICS4_V created.\n\n\n---------------------------\n","type":"TEXT"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":4,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"table","title":"Display data from DEMOGRAPHICS4_V view","message":["%sql ","","SELECT * FROM DEMOGRAPHICS4_V","FETCH FIRST 10 ROWS ONLY;"],"enabled":true,"result":{"startTime":1715224814867,"interpreter":"sql.medium","endTime":1715224814989,"results":[{"message":"AFFINITY_CARD\tCUST_ID\tBOOKKEEPING_APPLICATION\tBULK_PACK_DISKETTES\tEDUCATION\tFLAT_PANEL_MONITOR\tHOME_THEATER_PACKAGE\tHOUSEHOLD_SIZE\tOCCUPATION\tOS_DOC_SET_KANJI\tPRINTER_SUPPLIES\tYRS_RESIDENCE\tY_BOX_GAMES\n0\t102547\t0\t1\t10th\t1\t0\t1\tOther\t0\t1\t0\t1\n0\t101050\t0\t1\t10th\t1\t0\t1\tOther\t0\t1\t0\t1\n0\t100040\t0\t1\t11th\t1\t0\t1\tSales\t0\t1\t0\t1\n0\t102117\t1\t0\tHS-grad\t0\t0\t1\tFarming\t0\t1\t0\t1\n0\t101074\t0\t1\t10th\t1\t0\t1\tHandler\t0\t1\t1\t1\n0\t104179\t0\t1\t10th\t1\t0\t1\tHandler\t0\t1\t1\t1\n0\t100417\t0\t0\t11th\t0\t0\t1\tHandler\t0\t1\t1\t1\n0\t101146\t1\t1\t< Bach.\t1\t0\t1\t?\t0\t1\t1\t1\n0\t103420\t1\t1\t< Bach.\t1\t0\t1\t?\t0\t1\t1\t1\n0\t101987\t1\t1\t< Bach.\t1\t0\t1\tOther\t0\t1\t1\t1\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":8,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"raw","title":"Create train and test data sets (80/20) for model build and test","message":["%script","","CREATE OR REPLACE VIEW TRAIN_DATA_CLAS AS SELECT * FROM DEMOGRAPHICS4_V SAMPLE (80) SEED (1);","CREATE OR REPLACE VIEW TEST_DATA_CLAS AS SELECT * FROM DEMOGRAPHICS4_V MINUS SELECT * FROM TRAIN_DATA_CLAS;"],"enabled":true,"result":{"startTime":1715224815065,"interpreter":"script.medium","endTime":1715224815184,"results":[{"message":"\nView TRAIN_DATA_CLAS created.\n\n\n---------------------------\n\nView TEST_DATA_CLAS created.\n\n\n---------------------------\n","type":"TEXT"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":5,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"raw","title":"Build a Generalized Linear Model using default settings","message":["%script","","BEGIN DBMS_DATA_MINING.DROP_MODEL('GLM_CLASS_MODEL');","EXCEPTION WHEN OTHERS THEN NULL; END;","/","DECLARE"," v_setlst DBMS_DATA_MINING.SETTING_LIST;"," ","BEGIN"," v_setlst('PREP_AUTO') := 'ON';"," v_setlst('ALGO_NAME') := 'ALGO_GENERALIZED_LINEAR_MODEL';"," "," DBMS_DATA_MINING.CREATE_MODEL2("," 'GLM_CLASS_MODEL',"," 'CLASSIFICATION',"," 'SELECT * FROM TRAIN_DATA_CLAS',"," v_setlst,"," 'CUST_ID',"," 'AFFINITY_CARD');","END;"],"enabled":true,"result":{"startTime":1715224815263,"interpreter":"script.medium","endTime":1715224820364,"results":[{"message":"\nPL/SQL procedure successfully completed.\n\n\n---------------------------\n\nPL/SQL procedure successfully completed.\n\n","type":"TEXT"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":7,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"html","title":null,"message":["%md","","### Examples of possible setting overrides for GLM ","","If the user does not override the default settings, then relevant settings are determined by the algorithm.","","A complete list of settings can be found in the Documentation link:","","- Algorithm Settings: <a href=\"https://docs.oracle.com/pls/topic/lookup?ctx=en/database/oracle/oracle-database/23/arpls&id=ARPLS-GUID-4E3665B9-B1C2-4F6B-AB69-A7F353C70F5C\" onclick=\"return ! window.open('https://docs.oracle.com/pls/topic/lookup?ctx=en/database/oracle/oracle-database/23/arpls&id=ARPLS-GUID-4E3665B9-B1C2-4F6B-AB69-A7F353C70F5C');\">Generalized Linear Model<\/a>","","- Shared Settings: <a href=\"https://docs.oracle.com/en/database/oracle/oracle-database/23/arpls/DBMS_DATA_MINING.html#GUID-24047A09-0542-4870-91D8-329F28B0ED75\" onclick=\"return ! window.open('https://docs.oracle.com/en/database/oracle/oracle-database/23/arpls/DBMS_DATA_MINING.html#GUID-24047A09-0542-4870-91D8-329F28B0ED75');\">All algorithms<\/a>","","- Specify a row weight column ","> v_setlst('ODMS_ROW_WEIGHT_COLUMN_NAME') := '<row_weight_column_name>'; ","- Specify a missing value treatment method for the training data. This setting does not affect the scoring data. The default value is `ODMS_MISSING_VALUE_AUTO`. The option `ODMS_MISSING_VALUE_MEAN_MODE` replaces missing values with the mean (numeric attributes) or the mode (categorical attributes) both at build time and apply time where appropriate. The option `ODMS_MISSING_VALUE_AUTO` performs different strategies for different algorithms. When `ODMS_MISSING_VALUE_TREATMENT` is set to `ODMS_MISSING_VALUE_DELETE_ROW`, the rows in the training data that contain missing values are deleted. However, if you want to replicate this missing value treatment in the scoring data, then you must perform the transformation explicitly.","> v_setlst('ODMS_MISSING_VALUE_TREATMENT') := 'ODMS_MISSING_VALUE_AUTO';","- Specify The confidence level for coefficient confidence intervals. The value required is between 0 and 1 (excluding the edges), and the default confidence level is 0.95. ","> v_setlst('GLMS_CONF_LEVEL') := 'ODMS_MISSING_VALUE_DELETE_ROW';","- Turn ridge regression on or off. Enable or disable ridge regression. Ridge applies to both regression and classification machine learning functions. When ridge is enabled, prediction bounds are not produced by the `PREDICTION_BOUNDS` SQL function. Ridge may only be enabled when feature selection is not specified, or has been explicitly disabled. If ridge regression and feature selection are both explicitly enabled, then an exception is raised. By default the system turns it on if there is a multicollinearity.","> v_setlst('GLMS_RIDGE_REGRESSION') := 'GLMS_RIDGE_REG_DISABLE';","- Specify The value of the ridge parameter. This setting is only used when the algorithm is configured to use ridge regression. If ridge regression is enabled internally by the algorithm, then the ridge parameter is determined by the algorithm. It requires a value greater than 0 (exclusive).","> v_setlst('GLMS_RIDGE_VALUE') := '2';","- Turn feature selection on or off. By default the system does not do feature selection. Also, feature generation requires feature selection to be enabled. Some options for feature selection are show below","> v_setlst('GLMS_FTR_SELECTION') := 'GLMS_FTR_SELECTION_ENABLE';","- Specify whether or not feature generation is enabled for GLM. By default, feature generation is not enabled. Feature generation can only be enabled when feature selection is also enabled. ","> v_setlst('GLMS_FTR_GENERATION') := 'GLMS_FTR_GENERATION_ENABLE';","- Specify whether feature generation is quadratic or cubic. When feature generation is enabled, the algorithm automatically chooses the most appropriate feature generation method based on the data. Options are `GLMS_FTR_GEN_QUADRATIC` or `GLMS_FTR_GEN_CUBIC`","> v_setlst('GLMS_FTR_GEN_METHOD') := 'GLMS_FTR_GEN_QUADRATIC';","- Specify feature selection penalty criterion for adding a feature to the model. When feature selection is enabled, the algorithm automatically chooses the penalty criterion based on the data. Options are `GLMS_FTR_SEL_AIC`, `GLMS_FTR_SEL_SBIC`,`GLMS_FTR_SEL_RIC`,`GLMS_FTR_SEL_ALPHA_INV`. ","> v_setlst('GLMS_FTR_SEL_CRIT') := 'GLMS_FTR_SEL_ALPHA_INV';","- Specify the maximum number of features that can be selected for the final model (when feature selection is enabled). By default, the algorithm limits the number of features to ensure sufficient memory. It requires a number between 0 (exclusive) and 2000 (inclusive).","> v_setlst('GLMS_MAX_FEATURES') := '30';","- Specify whether prune is enabled or disabled for features in the final model. Pruning is based on T-Test statistics for linear regression, or Wald Test statistics for logistic regression. Features are pruned in a loop until all features are statistically significant with respect to the full data. When feature selection is enabled, the algorithm automatically performs pruning based on the data.","> v_setlst('GLMS_PRUNE_MODEL') := 'GLMS_PRUNE_MODEL_ENABLE';","- Specify the target value used as the `reference class` in a binary logistic regression model. Probabilities are produced for the other class. By default, the algorithm chooses the value with the highest prevalence (the most cases) for the reference class.","> v_setlst('GLMS_REFERENCE_CLASS_NAME') := '<target_value>';","- Specify whether to enable or disable row diagnostics. The default is `GLMS_ROW_DIAG_DISABLE`","> v_setlst('GLMS_ROW_DIAGNOSTICS') := 'GLMS_ROW_DIAG_ENABLE';","- Specify the convergence tolerance setting of the GLM algorithm. The default value is system-determined, and the value required is between 0 and 1 (exclusive).","> v_setlst('GLMS_CONV_TOLERANCE') := '0.0001';","- Specify the maximum number of iterations for the GLM algorithm. The default value is system-determined, and it requires a positive integer.","> v_setlst('GLMS_NUM_ITERATIONS') := '200';","- Specify the number of rows in a batch used by the SGD solver. The value of this parameter sets the size of the batch for the SGD solver. An input of 0 triggers a data driven batch size estimate, and it requires a positive integer. The default is 2,000.","> v_setlst('GLMS_BATCH_ROWS') := '2000';","- Specify the GLM solver to use. The solver cannot be selected if `GLMS_FTR_SELECTION` setting is enabled. The default value is system determined, and the options are `GLMS_SOLVER_SGD` (Stochastic Gradient Descent), `GLMS_SOLVER_CHOL` (Cholesky), `GLMS_SOLVER_QR` (QR decomposition) and `GLMS_SOLVER_LBFGS_ADMM` (LBFGS and ADAM)","> v_setlst('GLMS_SOLVER') := 'GLMS_SOLVER_SGD';","- Specify whether to use sparse solver if it is available. The default value is GLMS_SPARSE_SOLVER_DISABLE. ","> v_setlst('GLMS_SPARSE_SOLVER') := 'GLMS_SPARSE_SOLVER_ENABLE';","","<strong> For Oracle Database 23ai and newer. <\/strong>","- Specify which GLM Link Function to use. This setting allows the user to specify the link function for building a GLM model. The link functions are specific to the mining function. "," For classification, the options are `GLMS_LOGIT_LINK (default)`, `GLMS_PROBIT_LINK`, `GLMS_CLOGLOG_LINK` or `GLMS_CAUCHIT_LINK`. For regression, the default is `GLMS_IDENTITY_LINK (default)`.","> v_setlst('GLMS_LINK_FUNCTION') := 'GLMS_LOGIT_LINK';"],"enabled":true,"result":{"startTime":1715229722249,"interpreter":"md.medium","endTime":1715229722332,"results":[{"message":"<h3 id=\"examples-of-possible-setting-overrides-for-glm\">Examples of possible setting overrides for GLM<\/h3>\n<p>If the user does not override the default settings, then relevant settings are determined by the algorithm.<\/p>\n<p>A complete list of settings can be found in the Documentation link:<\/p>\n<ul>\n<li>\n<p>Algorithm Settings: <a href=\"https://docs.oracle.com/pls/topic/lookup?ctx=en/database/oracle/oracle-database/23/arpls&id=ARPLS-GUID-4E3665B9-B1C2-4F6B-AB69-A7F353C70F5C\" onclick=\"return ! window.open('https://docs.oracle.com/pls/topic/lookup?ctx=en/database/oracle/oracle-database/23/arpls&id=ARPLS-GUID-4E3665B9-B1C2-4F6B-AB69-A7F353C70F5C');\">Generalized Linear Model<\/a><\/p>\n<\/li>\n<li>\n<p>Shared Settings: <a href=\"https://docs.oracle.com/en/database/oracle/oracle-database/23/arpls/DBMS_DATA_MINING.html#GUID-24047A09-0542-4870-91D8-329F28B0ED75\" onclick=\"return ! window.open('https://docs.oracle.com/en/database/oracle/oracle-database/23/arpls/DBMS_DATA_MINING.html#GUID-24047A09-0542-4870-91D8-329F28B0ED75');\">All algorithms<\/a><\/p>\n<\/li>\n<li>\n<p>Specify a row weight column<\/p>\n<\/li>\n<\/ul>\n<blockquote>\n<p>v_setlst('ODMS_ROW_WEIGHT_COLUMN_NAME') := '<row_weight_column_name>';<\/p>\n<\/blockquote>\n<ul>\n<li>Specify a missing value treatment method for the training data. This setting does not affect the scoring data. The default value is <code>ODMS_MISSING_VALUE_AUTO<\/code>. The option <code>ODMS_MISSING_VALUE_MEAN_MODE<\/code> replaces missing values with the mean (numeric attributes) or the mode (categorical attributes) both at build time and apply time where appropriate. The option <code>ODMS_MISSING_VALUE_AUTO<\/code> performs different strategies for different algorithms. When <code>ODMS_MISSING_VALUE_TREATMENT<\/code> is set to <code>ODMS_MISSING_VALUE_DELETE_ROW<\/code>, the rows in the training data that contain missing values are deleted. However, if you want to replicate this missing value treatment in the scoring data, then you must perform the transformation explicitly.<\/li>\n<\/ul>\n<blockquote>\n<p>v_setlst('ODMS_MISSING_VALUE_TREATMENT') := 'ODMS_MISSING_VALUE_AUTO';<\/p>\n<\/blockquote>\n<ul>\n<li>Specify The confidence level for coefficient confidence intervals. The value required is between 0 and 1 (excluding the edges), and the default confidence level is 0.95.<\/li>\n<\/ul>\n<blockquote>\n<p>v_setlst('GLMS_CONF_LEVEL') := 'ODMS_MISSING_VALUE_DELETE_ROW';<\/p>\n<\/blockquote>\n<ul>\n<li>Turn ridge regression on or off. Enable or disable ridge regression. Ridge applies to both regression and classification machine learning functions. When ridge is enabled, prediction bounds are not produced by the <code>PREDICTION_BOUNDS<\/code> SQL function. Ridge may only be enabled when feature selection is not specified, or has been explicitly disabled. If ridge regression and feature selection are both explicitly enabled, then an exception is raised. By default the system turns it on if there is a multicollinearity.<\/li>\n<\/ul>\n<blockquote>\n<p>v_setlst('GLMS_RIDGE_REGRESSION') := 'GLMS_RIDGE_REG_DISABLE';<\/p>\n<\/blockquote>\n<ul>\n<li>Specify The value of the ridge parameter. This setting is only used when the algorithm is configured to use ridge regression. If ridge regression is enabled internally by the algorithm, then the ridge parameter is determined by the algorithm. It requires a value greater than 0 (exclusive).<\/li>\n<\/ul>\n<blockquote>\n<p>v_setlst('GLMS_RIDGE_VALUE') := '2';<\/p>\n<\/blockquote>\n<ul>\n<li>Turn feature selection on or off. By default the system does not do feature selection. Also, feature generation requires feature selection to be enabled. Some options for feature selection are show below<\/li>\n<\/ul>\n<blockquote>\n<p>v_setlst('GLMS_FTR_SELECTION') := 'GLMS_FTR_SELECTION_ENABLE';<\/p>\n<\/blockquote>\n<ul>\n<li>Specify whether or not feature generation is enabled for GLM. By default, feature generation is not enabled. Feature generation can only be enabled when feature selection is also enabled.<\/li>\n<\/ul>\n<blockquote>\n<p>v_setlst('GLMS_FTR_GENERATION') := 'GLMS_FTR_GENERATION_ENABLE';<\/p>\n<\/blockquote>\n<ul>\n<li>Specify whether feature generation is quadratic or cubic. When feature generation is enabled, the algorithm automatically chooses the most appropriate feature generation method based on the data. Options are <code>GLMS_FTR_GEN_QUADRATIC<\/code> or <code>GLMS_FTR_GEN_CUBIC<\/code><\/li>\n<\/ul>\n<blockquote>\n<p>v_setlst('GLMS_FTR_GEN_METHOD') := 'GLMS_FTR_GEN_QUADRATIC';<\/p>\n<\/blockquote>\n<ul>\n<li>Specify feature selection penalty criterion for adding a feature to the model. When feature selection is enabled, the algorithm automatically chooses the penalty criterion based on the data. Options are <code>GLMS_FTR_SEL_AIC<\/code>, <code>GLMS_FTR_SEL_SBIC<\/code>,<code>GLMS_FTR_SEL_RIC<\/code>,<code>GLMS_FTR_SEL_ALPHA_INV<\/code>.<\/li>\n<\/ul>\n<blockquote>\n<p>v_setlst('GLMS_FTR_SEL_CRIT') := 'GLMS_FTR_SEL_ALPHA_INV';<\/p>\n<\/blockquote>\n<ul>\n<li>Specify the maximum number of features that can be selected for the final model (when feature selection is enabled). By default, the algorithm limits the number of features to ensure sufficient memory. It requires a number between 0 (exclusive) and 2000 (inclusive).<\/li>\n<\/ul>\n<blockquote>\n<p>v_setlst('GLMS_MAX_FEATURES') := '30';<\/p>\n<\/blockquote>\n<ul>\n<li>Specify whether prune is enabled or disabled for features in the final model. Pruning is based on T-Test statistics for linear regression, or Wald Test statistics for logistic regression. Features are pruned in a loop until all features are statistically significant with respect to the full data. When feature selection is enabled, the algorithm automatically performs pruning based on the data.<\/li>\n<\/ul>\n<blockquote>\n<p>v_setlst('GLMS_PRUNE_MODEL') := 'GLMS_PRUNE_MODEL_ENABLE';<\/p>\n<\/blockquote>\n<ul>\n<li>Specify the target value used as the <code>reference class<\/code> in a binary logistic regression model. Probabilities are produced for the other class. By default, the algorithm chooses the value with the highest prevalence (the most cases) for the reference class.<\/li>\n<\/ul>\n<blockquote>\n<p>v_setlst('GLMS_REFERENCE_CLASS_NAME') := '<target_value>';<\/p>\n<\/blockquote>\n<ul>\n<li>Specify whether to enable or disable row diagnostics. The default is <code>GLMS_ROW_DIAG_DISABLE<\/code><\/li>\n<\/ul>\n<blockquote>\n<p>v_setlst('GLMS_ROW_DIAGNOSTICS') := 'GLMS_ROW_DIAG_ENABLE';<\/p>\n<\/blockquote>\n<ul>\n<li>Specify the convergence tolerance setting of the GLM algorithm. The default value is system-determined, and the value required is between 0 and 1 (exclusive).<\/li>\n<\/ul>\n<blockquote>\n<p>v_setlst('GLMS_CONV_TOLERANCE') := '0.0001';<\/p>\n<\/blockquote>\n<ul>\n<li>Specify the maximum number of iterations for the GLM algorithm. The default value is system-determined, and it requires a positive integer.<\/li>\n<\/ul>\n<blockquote>\n<p>v_setlst('GLMS_NUM_ITERATIONS') := '200';<\/p>\n<\/blockquote>\n<ul>\n<li>Specify the number of rows in a batch used by the SGD solver. The value of this parameter sets the size of the batch for the SGD solver. An input of 0 triggers a data driven batch size estimate, and it requires a positive integer. The default is 2,000.<\/li>\n<\/ul>\n<blockquote>\n<p>v_setlst('GLMS_BATCH_ROWS') := '2000';<\/p>\n<\/blockquote>\n<ul>\n<li>Specify the GLM solver to use. The solver cannot be selected if <code>GLMS_FTR_SELECTION<\/code> setting is enabled. The default value is system determined, and the options are <code>GLMS_SOLVER_SGD<\/code> (Stochastic Gradient Descent), <code>GLMS_SOLVER_CHOL<\/code> (Cholesky), <code>GLMS_SOLVER_QR<\/code> (QR decomposition) and <code>GLMS_SOLVER_LBFGS_ADMM<\/code> (LBFGS and ADAM)<\/li>\n<\/ul>\n<blockquote>\n<p>v_setlst('GLMS_SOLVER') := 'GLMS_SOLVER_SGD';<\/p>\n<\/blockquote>\n<ul>\n<li>Specify whether to use sparse solver if it is available. The default value is GLMS_SPARSE_SOLVER_DISABLE.<\/li>\n<\/ul>\n<blockquote>\n<p>v_setlst('GLMS_SPARSE_SOLVER') := 'GLMS_SPARSE_SOLVER_ENABLE';<\/p>\n<\/blockquote>\n<p><strong> For Oracle Database 23ai and newer. <\/strong><\/p>\n<ul>\n<li>Specify which GLM Link Function to use. This setting allows the user to specify the link function for building a GLM model. The link functions are specific to the mining function.\nFor classification, the options are <code>GLMS_LOGIT_LINK (default)<\/code>, <code>GLMS_PROBIT_LINK<\/code>, <code>GLMS_CLOGLOG_LINK<\/code> or <code>GLMS_CAUCHIT_LINK<\/code>. For regression, the default is <code>GLMS_IDENTITY_LINK (default)<\/code>.<\/li>\n<\/ul>\n<blockquote>\n<p>v_setlst('GLMS_LINK_FUNCTION') := 'GLMS_LOGIT_LINK';<\/p>\n<\/blockquote>\n","type":"HTML"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":true,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":false,"hideVizConfig":true,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"raw","title":"Build a Generalized Linear Model with feature generation and feature selection","message":["%script","","BEGIN DBMS_DATA_MINING.DROP_MODEL('GLM_CLASS_MODEL');","EXCEPTION WHEN OTHERS THEN NULL; END;","/","DECLARE"," v_setlst DBMS_DATA_MINING.SETTING_LIST;"," ","BEGIN"," v_setlst('GLMS_DIAGNOSTICS_TABLE_NAME') := 'GLMR_SH_SAMPLE_DIAG';"," v_setlst('GLMS_RIDGE_REGRESSION') := 'GLMS_RIDGE_REG_DISABLE';"," v_setlst('GLMS_FTR_SELECTION') := 'GLMS_FTR_SELECTION_ENABLE';"," v_setlst('GLMS_FTR_GEN_METHOD') := 'GLMS_FTR_GEN_QUADRATIC';"," v_setlst('GLMS_FTR_GENERATION') := 'GLMS_FTR_GENERATION_ENABLE'; "," v_setlst('GLMS_FTR_SEL_CRIT') := 'GLMS_FTR_SEL_AIC';"," v_setlst('GLMS_NUM_ITERATIONS') := '50';"," v_setlst('GLMS_MAX_FEATURES') := '30';"," v_setlst('GLMS_PRUNE_MODEL') := 'GLMS_PRUNE_MODEL_ENABLE';"," v_setlst('GLMS_ROW_DIAGNOSTICS') := 'GLMS_ROW_DIAG_DISABLE';"," v_setlst('GLMS_CONV_TOLERANCE') := '0.0001';"," v_setlst('GLMS_BATCH_ROWS') := '2000';"," v_setlst('ODMS_MISSING_VALUE_TREATMENT') := 'ODMS_MISSING_VALUE_DELETE_ROW';","",""," DBMS_DATA_MINING.CREATE_MODEL2("," 'GLM_CLASS_MODEL',"," 'CLASSIFICATION',"," 'SELECT * FROM TRAIN_DATA_CLAS',"," v_setlst,"," 'CUST_ID',"," 'AFFINITY_CARD');","END;"],"enabled":true,"result":{"startTime":1715224820615,"interpreter":"script.medium","endTime":1715224822516,"results":[{"message":"\nPL/SQL procedure successfully completed.\n\n\n---------------------------\n\nPL/SQL procedure successfully completed.\n\n","type":"TEXT"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":6,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"raw","title":"Build a Generalized Linear Model with sparse, SGD solver","message":["%script","","BEGIN DBMS_DATA_MINING.DROP_MODEL('GLM_CLASS_MODEL');","EXCEPTION WHEN OTHERS THEN NULL; END;","/","DECLARE"," v_setlst DBMS_DATA_MINING.SETTING_LIST;"," ","BEGIN"," v_setlst('PREP_AUTO') := 'ON';"," v_setlst('ALGO_NAME') := 'ALGO_GENERALIZED_LINEAR_MODEL';"," v_setlst('GLMS_SOLVER') := 'GLMS_SOLVER_SGD';"," v_setlst('GLMS_SPARSE_SOLVER') := 'GLMS_SPARSE_SOLVER_ENABLE';"," v_setlst('GLMS_NUM_ITERATIONS') := '50';"," v_setlst('GLMS_ROW_DIAGNOSTICS') := 'GLMS_ROW_DIAG_DISABLE';"," v_setlst('GLMS_CONV_TOLERANCE') := '0.0001';"," v_setlst('GLMS_BATCH_ROWS') := '2000';","",""," DBMS_DATA_MINING.CREATE_MODEL2("," 'GLM_CLASS_MODEL',"," 'CLASSIFICATION',"," 'SELECT * FROM TRAIN_DATA_CLAS',"," v_setlst,"," 'CUST_ID',"," 'AFFINITY_CARD');","END;"],"enabled":true,"result":{"startTime":1715224822604,"interpreter":"script.medium","endTime":1715224824506,"results":[{"message":"\nPL/SQL procedure successfully completed.\n\n\n---------------------------\n\nPL/SQL procedure successfully completed.\n\n","type":"TEXT"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":6,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"raw","title":"Build a Generalized Linear Model with ridge regression","message":["%script","","BEGIN DBMS_DATA_MINING.DROP_MODEL('GLM_CLASS_MODEL');","EXCEPTION WHEN OTHERS THEN NULL; END;","/","DECLARE"," v_setlst DBMS_DATA_MINING.SETTING_LIST;"," ","BEGIN"," v_setlst('PREP_AUTO') := 'ON';"," v_setlst('ALGO_NAME') := 'ALGO_GENERALIZED_LINEAR_MODEL';"," v_setlst('GLMS_DIAGNOSTICS_TABLE_NAME') := 'GLMR_SH_SAMPLE_DIAG';"," v_setlst('GLMS_RIDGE_REGRESSION') := 'GLMS_RIDGE_REG_ENABLE';"," v_setlst('GLMS_RIDGE_VALUE') := '2';"," v_setlst('GLMS_FTR_SELECTION') := 'GLMS_FTR_SELECTION_DISABLE';"," v_setlst('GLMS_NUM_ITERATIONS') := '50';"," v_setlst('GLMS_ROW_DIAGNOSTICS') := 'GLMS_ROW_DIAG_DISABLE';"," v_setlst('GLMS_CONV_TOLERANCE') := '0.0001';"," v_setlst('GLMS_BATCH_ROWS') := '2000';",""," DBMS_DATA_MINING.CREATE_MODEL2("," 'GLM_CLASS_MODEL',"," 'CLASSIFICATION',"," 'SELECT * FROM TRAIN_DATA_CLAS',"," v_setlst,"," 'CUST_ID',"," 'AFFINITY_CARD');","END;"],"enabled":true,"result":{"startTime":1715224824586,"interpreter":"script.medium","endTime":1715224826788,"results":[{"message":"\nPL/SQL procedure successfully completed.\n\n\n---------------------------\n\nPL/SQL procedure successfully completed.\n\n","type":"TEXT"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":6,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"table","title":"Get list of model views","message":["%sql ","","SELECT VIEW_NAME, VIEW_TYPE ","FROM USER_MINING_MODEL_VIEWS","WHERE MODEL_NAME='GLM_CLASS_MODEL'","ORDER BY VIEW_NAME;"," "],"enabled":true,"result":{"startTime":1715224826865,"interpreter":"sql.medium","endTime":1715224826987,"results":[{"message":"VIEW_NAME\tVIEW_TYPE\nDM$VAGLM_CLASS_MODEL\tGLM Classification Row Diagnostics\nDM$VCGLM_CLASS_MODEL\tScoring Cost Matrix\nDM$VDGLM_CLASS_MODEL\tGLM Classification Attribute Diagnostics\nDM$VGGLM_CLASS_MODEL\tGlobal Name-Value Pairs\nDM$VNGLM_CLASS_MODEL\tNormalization and Missing Value Handling\nDM$VSGLM_CLASS_MODEL\tComputed Settings\nDM$VTGLM_CLASS_MODEL\tClassification Targets\nDM$VWGLM_CLASS_MODEL\tModel Build Alerts\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":6,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"table","title":"Get model fit statistics","message":["%sql","","SELECT NAME, round(NUMERIC_VALUE, 4) NUMERIC_VALUE, STRING_VALUE","FROM DM$VGGLM_CLASS_MODEL;","ORDER BY 1;"],"enabled":true,"result":{"startTime":1715224827065,"interpreter":"sql.medium","endTime":1715224827185,"results":[{"message":"NAME\tNUMERIC_VALUE\tSTRING_VALUE\nAIC_INTERCEPT\t3903.4939\t\nAIC_MODEL\t2562.2387\t\nCONVERGED\t\tNO\nDEPENDENT_MEAN\t0.2323\t\nITERATIONS\t7\t\nLR_CHI_SQ\t1423.2552\t\nLR_CHI_SQ_P_VALUE\t0\t\nLR_DF\t41\t\nNEG2_LL_INTERCEPT\t3901.4939\t\nNEG2_LL_MODEL\t2478.2387\t\nNUM_PARAMS\t42\t\nNUM_ROWS\t3599\t\nPCT_CORRECT\t0.8422\t\nPCT_INCORRECT\t0.1578\t\nPCT_TIED\t0\t\nPSEUDO_R_SQ_CS\t0.3266\t\nPSEUDO_R_SQ_N\t0.4936\t\nRANK_DEFICIENCY\t1\t\nSC_INTERCEPT\t3909.6823\t\nSC_MODEL\t2822.152\t\nVALID_COVARIANCE_MATRIX\t\tYES\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":6,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"table","title":"Get fit details for each attribute","message":["%sql","","SELECT ATTRIBUTE_NAME, ATTRIBUTE_VALUE, round(COEFFICIENT,3) COEFFICIENT, round(P_VALUE , 3) P_VALUE","FROM DM$VDGLM_CLASS_MODEL","ORDER BY FEATURE_EXPRESSION;"," "],"enabled":true,"result":{"startTime":1715224827265,"interpreter":"sql.medium","endTime":1715224827450,"results":[{"message":"ATTRIBUTE_NAME\tATTRIBUTE_VALUE\tCOEFFICIENT\tP_VALUE\n\t\t-1.926\t0\nOCCUPATION\tTransp.\t-0.818\t1\nBULK_PACK_DISKETTES\t\t0.239\t0.24\nFLAT_PANEL_MONITOR\t\t-0.282\t0.155\nHOME_THEATER_PACKAGE\t\t0.171\t0.263\nOS_DOC_SET_KANJI\t\t-3.792\t0.44\nPRINTER_SUPPLIES\t\t0\t1\nYRS_RESIDENCE\t\t0.16\t0\nY_BOX_GAMES\t\t-0.98\t0\nEDUCATION\t10th\t0.41\t0.386\nEDUCATION\t11th\t0.658\t0.152\nEDUCATION\t12th\t-0.162\t0.779\nEDUCATION\t1st-4th\t-0.366\t0.555\nEDUCATION\t5th-6th\t-0.436\t0.471\nEDUCATION\t7th-8th\t-0.07\t0.878\nEDUCATION\t9th\t-0.036\t0.94\nEDUCATION\t< Bach.\t0.323\t0.018\nEDUCATION\tAssoc-A\t0.671\t0.004\nEDUCATION\tAssoc-V\t0.289\t0.201\nEDUCATION\tBach.\t0.831\t0\nEDUCATION\tMasters\t1.223\t0\nEDUCATION\tPhD\t1.246\t0.001\nEDUCATION\tPresch.\t-0.101\t0.882\nEDUCATION\tProfsc\t1.113\t0\nHOUSEHOLD_SIZE\t1\t-2.273\t0\nHOUSEHOLD_SIZE\t2\t-2.151\t0\nHOUSEHOLD_SIZE\t4-5\t0.388\t0.032\nHOUSEHOLD_SIZE\t6-8\t-2.188\t0\nHOUSEHOLD_SIZE\t9+\t-2.323\t0\nOCCUPATION\t?\t-1.126\t0\nOCCUPATION\tArmed-F\t0.103\t0.88\nOCCUPATION\tCleric.\t-0.408\t0.035\nOCCUPATION\tCrafts\t-0.75\t0\nOCCUPATION\tFarming\t-1.222\t0\nOCCUPATION\tHandler\t-1.283\t0\nOCCUPATION\tHouse-s\t-0.389\t0.524\nOCCUPATION\tMachine\t-0.741\t0.001\nOCCUPATION\tOther\t-1.277\t0\nOCCUPATION\tProf.\t0.127\t0.44\nOCCUPATION\tProtec.\t-0.035\t0.903\nOCCUPATION\tSales\t-0.441\t0.007\nOCCUPATION\tTechSup\t-0.221\t0.358\nBOOKKEEPING_APPLICATION\t\t1.023\t0.006\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"raw","title":"Evaluate the model","message":["%script","","BEGIN EXECUTE IMMEDIATE 'DROP TABLE APPLY_RESULT PURGE';","EXCEPTION WHEN OTHERS THEN NULL; END;","/","BEGIN EXECUTE IMMEDIATE 'DROP TABLE LIFT_TABLE PURGE';","EXCEPTION WHEN OTHERS THEN NULL; END;","/","BEGIN"," DBMS_DATA_MINING.APPLY('GLM_CLASS_MODEL','TEST_DATA_CLAS','CUST_ID','APPLY_RESULT');"," DBMS_DATA_MINING.COMPUTE_LIFT('APPLY_RESULT','TEST_DATA_CLAS','CUST_ID','AFFINITY_CARD',"," 'LIFT_TABLE','1','PREDICTION','PROBABILITY',100);","END;"],"enabled":true,"result":{"startTime":1715224827536,"interpreter":"script.medium","endTime":1715224831537,"results":[{"message":"\nPL/SQL procedure successfully completed.\n\n\n---------------------------\n\nPL/SQL procedure successfully completed.\n\n\n---------------------------\n\nPL/SQL procedure successfully completed.\n\n","type":"TEXT"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":7,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":"[{\"table\":{\"version\":1},\"line\":{\"showSeries\":[\"GAIN_CUMULATIVE\"],\"series\":{\"availableSeriesElements\":[{\"id\":\"GAIN_CUMULATIVE\",\"lineType\":\"straight\",\"borderColor\":\"rgb(25, 95, 116)\",\"borderWidth\":0,\"color\":\"rgb(25, 95, 116)\",\"pattern\":\"auto\",\"markerColor\":\"rgb(25, 95, 116)\",\"markerDisplayed\":\"auto\",\"markerShape\":\"auto\",\"markerSize\":0}]},\"lastColumns\":[\"QUANTILE_NUMBER\",\"GAIN_CUMULATIVE\"],\"version\":1}}]","hideInIFrame":false,"selectedVisualization":"line","title":"View model's cumulative gains (lift) chart","message":["%sql","","SELECT QUANTILE_NUMBER, "," ROUND(GAIN_CUMULATIVE,3) GAIN_CUMULATIVE","FROM LIFT_TABLE;"],"enabled":true,"result":{"startTime":1715224831614,"interpreter":"sql.medium","endTime":1715224831737,"results":[{"message":"QUANTILE_NUMBER\tGAIN_CUMULATIVE\n1\t0.05\n2\t0.094\n3\t0.139\n4\t0.173\n5\t0.21\n6\t0.238\n7\t0.272\n8\t0.312\n9\t0.337\n10\t0.361\n11\t0.386\n12\t0.411\n13\t0.436\n14\t0.465\n15\t0.495\n16\t0.505\n17\t0.525\n18\t0.545\n19\t0.569\n20\t0.584\n21\t0.614\n22\t0.629\n23\t0.644\n24\t0.668\n25\t0.683\n26\t0.693\n27\t0.718\n28\t0.728\n29\t0.738\n30\t0.75\n31\t0.763\n32\t0.771\n33\t0.782\n34\t0.797\n35\t0.807\n36\t0.817\n37\t0.832\n38\t0.837\n39\t0.851\n40\t0.871\n41\t0.881\n42\t0.891\n43\t0.896\n44\t0.896\n45\t0.896\n46\t0.906\n47\t0.916\n48\t0.916\n49\t0.921\n50\t0.926\n51\t0.931\n52\t0.931\n53\t0.936\n54\t0.95\n55\t0.95\n56\t0.95\n57\t0.95\n58\t0.95\n59\t0.955\n60\t0.96\n61\t0.96\n62\t0.965\n63\t0.973\n64\t0.975\n65\t0.975\n66\t0.98\n67\t0.98\n68\t0.98\n69\t0.98\n70\t0.98\n71\t0.98\n72\t0.98\n73\t0.98\n74\t0.98\n75\t0.99\n76\t1\n77\t1\n78\t1\n79\t1\n80\t1\n81\t1\n82\t1\n83\t1\n84\t1\n85\t1\n86\t1\n87\t1\n88\t1\n89\t1\n90\t1\n91\t1\n92\t1\n93\t1\n94\t1\n95\t1\n96\t1\n97\t1\n98\t1\n99\t1\n100\t1\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":5,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"table","title":"Display all customers with likelihood > 70% to be AFFINITY_CARD responders","message":["%sql","","SELECT CUST_ID, PREDICTION PRED, ROUND(PROBABILITY,3) PROB, ROUND(COST,2) COST "," FROM APPLY_RESULT WHERE PREDICTION = 1 AND PROBABILITY > 0.7 "," ORDER BY PROBABILITY DESC;"," "],"enabled":true,"result":{"startTime":1715224831817,"interpreter":"sql.medium","endTime":1715224831938,"results":[{"message":"CUST_ID\tPRED\tPROB\tCOST\n101708\t1\t0.848\t0.15\n103031\t1\t0.841\t0.16\n104206\t1\t0.836\t0.16\n100179\t1\t0.833\t0.17\n100100\t1\t0.832\t0.17\n100985\t1\t0.829\t0.17\n101676\t1\t0.827\t0.17\n102435\t1\t0.823\t0.18\n101885\t1\t0.821\t0.18\n103579\t1\t0.817\t0.18\n100816\t1\t0.814\t0.19\n103755\t1\t0.812\t0.19\n100780\t1\t0.811\t0.19\n102585\t1\t0.808\t0.19\n102669\t1\t0.806\t0.19\n102661\t1\t0.806\t0.19\n104064\t1\t0.806\t0.19\n100889\t1\t0.805\t0.2\n103692\t1\t0.805\t0.2\n101972\t1\t0.802\t0.2\n101469\t1\t0.797\t0.2\n100988\t1\t0.793\t0.21\n102022\t1\t0.793\t0.21\n104093\t1\t0.793\t0.21\n100644\t1\t0.793\t0.21\n103320\t1\t0.792\t0.21\n101781\t1\t0.784\t0.22\n102796\t1\t0.781\t0.22\n104131\t1\t0.778\t0.22\n102266\t1\t0.778\t0.22\n104364\t1\t0.778\t0.22\n101439\t1\t0.778\t0.22\n104419\t1\t0.777\t0.22\n102177\t1\t0.777\t0.22\n103463\t1\t0.777\t0.22\n100073\t1\t0.775\t0.22\n104251\t1\t0.771\t0.23\n102087\t1\t0.767\t0.23\n103880\t1\t0.766\t0.23\n102401\t1\t0.764\t0.24\n100817\t1\t0.763\t0.24\n103838\t1\t0.763\t0.24\n100372\t1\t0.759\t0.24\n100230\t1\t0.758\t0.24\n100066\t1\t0.758\t0.24\n102615\t1\t0.756\t0.24\n101564\t1\t0.755\t0.24\n104320\t1\t0.753\t0.25\n101581\t1\t0.751\t0.25\n102447\t1\t0.742\t0.26\n103898\t1\t0.742\t0.26\n101191\t1\t0.742\t0.26\n104369\t1\t0.742\t0.26\n101635\t1\t0.742\t0.26\n103204\t1\t0.741\t0.26\n101229\t1\t0.739\t0.26\n101730\t1\t0.737\t0.26\n101793\t1\t0.736\t0.26\n102183\t1\t0.73\t0.27\n102351\t1\t0.727\t0.27\n103931\t1\t0.722\t0.28\n100173\t1\t0.72\t0.28\n100954\t1\t0.716\t0.28\n101504\t1\t0.711\t0.29\n100274\t1\t0.711\t0.29\n101260\t1\t0.711\t0.29\n100404\t1\t0.711\t0.29\n103830\t1\t0.71\t0.29\n102120\t1\t0.707\t0.29\n100272\t1\t0.704\t0.3\n103090\t1\t0.704\t0.3\n104197\t1\t0.703\t0.3\n103457\t1\t0.702\t0.3\n103708\t1\t0.702\t0.3\n100406\t1\t0.702\t0.3\n100293\t1\t0.702\t0.3\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":6,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"table","title":"Select and view likely and unlikely AFFINITY_CARD responders ","message":["%sql ","","SELECT CUST_ID, PREDICTION, ROUND(PROBABILITY,2) PROB, ROUND(COST,2) COST"," FROM APPLY_RESULT WHERE PREDICTION = ${PREDICTION='1','1'|'0'} "," AND PROBABILITY > 0.7 ORDER BY PROBABILITY DESC;"," "],"enabled":true,"result":{"startTime":1715224832020,"interpreter":"sql.medium","endTime":1715224832144,"results":[{"message":"CUST_ID\tPREDICTION\tPROB\tCOST\n101708\t1\t0.85\t0.15\n103031\t1\t0.84\t0.16\n104206\t1\t0.84\t0.16\n100179\t1\t0.83\t0.17\n100100\t1\t0.83\t0.17\n100985\t1\t0.83\t0.17\n101676\t1\t0.83\t0.17\n102435\t1\t0.82\t0.18\n101885\t1\t0.82\t0.18\n103579\t1\t0.82\t0.18\n100816\t1\t0.81\t0.19\n103755\t1\t0.81\t0.19\n100780\t1\t0.81\t0.19\n102585\t1\t0.81\t0.19\n104064\t1\t0.81\t0.19\n102669\t1\t0.81\t0.19\n102661\t1\t0.81\t0.19\n103692\t1\t0.8\t0.2\n100889\t1\t0.8\t0.2\n101972\t1\t0.8\t0.2\n101469\t1\t0.8\t0.2\n102022\t1\t0.79\t0.21\n100988\t1\t0.79\t0.21\n104093\t1\t0.79\t0.21\n100644\t1\t0.79\t0.21\n103320\t1\t0.79\t0.21\n101781\t1\t0.78\t0.22\n102796\t1\t0.78\t0.22\n104131\t1\t0.78\t0.22\n102266\t1\t0.78\t0.22\n104364\t1\t0.78\t0.22\n101439\t1\t0.78\t0.22\n102177\t1\t0.78\t0.22\n104419\t1\t0.78\t0.22\n103463\t1\t0.78\t0.22\n100073\t1\t0.78\t0.22\n104251\t1\t0.77\t0.23\n102087\t1\t0.77\t0.23\n103880\t1\t0.77\t0.23\n102401\t1\t0.76\t0.24\n100817\t1\t0.76\t0.24\n103838\t1\t0.76\t0.24\n100372\t1\t0.76\t0.24\n100066\t1\t0.76\t0.24\n100230\t1\t0.76\t0.24\n102615\t1\t0.76\t0.24\n101564\t1\t0.76\t0.24\n104320\t1\t0.75\t0.25\n101581\t1\t0.75\t0.25\n102447\t1\t0.74\t0.26\n101635\t1\t0.74\t0.26\n101191\t1\t0.74\t0.26\n104369\t1\t0.74\t0.26\n103898\t1\t0.74\t0.26\n103204\t1\t0.74\t0.26\n101229\t1\t0.74\t0.26\n101730\t1\t0.74\t0.26\n101793\t1\t0.74\t0.26\n102183\t1\t0.73\t0.27\n102351\t1\t0.73\t0.27\n103931\t1\t0.72\t0.28\n100173\t1\t0.72\t0.28\n100954\t1\t0.72\t0.28\n101260\t1\t0.71\t0.29\n101504\t1\t0.71\t0.29\n100404\t1\t0.71\t0.29\n100274\t1\t0.71\t0.29\n103830\t1\t0.71\t0.29\n102120\t1\t0.71\t0.29\n100272\t1\t0.7\t0.3\n103090\t1\t0.7\t0.3\n104197\t1\t0.7\t0.3\n103457\t1\t0.7\t0.3\n103708\t1\t0.7\t0.3\n100293\t1\t0.7\t0.3\n100406\t1\t0.7\t0.3\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":6,"hideResult":false,"dynamicFormParams":"{\"PREDICTION\":\"'1'\"}","row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[{\"type\":\"Select\",\"name\":\"PREDICTION\",\"displayName\":null,\"defaultValue\":\"'1'\",\"argument\":null,\"options\":[{\"value\":\"'1'\",\"displayName\":null},{\"value\":\"'0'\",\"displayName\":null}],\"isHidden\":false,\"isProgrammatic\":false}]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"table","title":"Use SQL query to dynamically score and select customers with >70% probability of being AFFINITY_CARD a responders","message":["%sql","","SELECT * ","FROM (SELECT CUST_ID, "," ROUND(PREDICTION_PROBABILITY(GLM_CLASS_MODEL, '1' USING A.*),3) PROBABILITY"," FROM TEST_DATA_CLAS A)","WHERE PROBABILITY > 0.7;"],"enabled":true,"result":{"startTime":1715224832226,"interpreter":"sql.medium","endTime":1715224832376,"results":[{"message":"CUST_ID\tPROBABILITY\n100230\t0.758\n100954\t0.716\n101191\t0.742\n103457\t0.702\n100073\t0.775\n100100\t0.832\n100816\t0.814\n100817\t0.763\n101708\t0.848\n101972\t0.802\n102669\t0.806\n103031\t0.841\n103090\t0.704\n103692\t0.805\n103708\t0.702\n103755\t0.812\n103898\t0.742\n104064\t0.806\n104131\t0.778\n101635\t0.742\n102183\t0.73\n100173\t0.72\n100644\t0.793\n100780\t0.811\n101730\t0.737\n101793\t0.736\n102022\t0.793\n102435\t0.823\n103880\t0.766\n102120\t0.707\n103320\t0.792\n100272\t0.704\n100404\t0.711\n101781\t0.784\n102266\t0.778\n102401\t0.764\n103579\t0.817\n103838\t0.763\n103931\t0.722\n104419\t0.777\n100274\t0.711\n101439\t0.778\n102585\t0.808\n104197\t0.703\n100293\t0.702\n100372\t0.759\n100889\t0.805\n101504\t0.711\n101581\t0.751\n102177\t0.777\n102351\t0.727\n103463\t0.777\n104093\t0.793\n104320\t0.753\n101229\t0.739\n102447\t0.742\n102615\t0.756\n104251\t0.771\n100066\t0.758\n100179\t0.833\n100988\t0.793\n101260\t0.711\n101564\t0.755\n101676\t0.827\n101885\t0.821\n102087\t0.767\n102661\t0.806\n102796\t0.781\n103204\t0.741\n103830\t0.71\n104206\t0.836\n104364\t0.778\n100406\t0.702\n100985\t0.829\n101469\t0.797\n104369\t0.742\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":6,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"raw","title":"Apply the model to a single new record","message":["%sql","","-- Predict the probability of being a high AFFINITY_CARD responder (1) ","-- by providing specific attribute values for a customer.","","SELECT ROUND(PREDICTION_PROBABILITY(GLM_CLASS_MODEL, '1' USING "," '3' AS HOUSEHOLD_SIZE, "," 5 AS YRS_RESIDENCE, "," 1 AS Y_BOX_GAMES),3) PROBABILITY_AFFINITY_CARD_RESPONDER","FROM DUAL;"," "],"enabled":true,"result":{"startTime":1715224832454,"interpreter":"sql.medium","endTime":1715224832528,"results":[{"message":"PROBABILITY_AFFINITY_CARD_RESPONDER\n0.245\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":6,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"table","title":"Get prediction details","message":["%sql","","SELECT CUST_ID,"," round(PREDICTION_AFFINITY_CARD,3) PRED_AFFINITY_CARD,"," RTRIM(TRIM(SUBSTR(OUTPRED.\"Attribute1\",17,100)),'rank=\"1\"/>') FIRST_ATTRIBUTE,"," RTRIM(TRIM(SUBSTR(OUTPRED.\"Attribute2\",17,100)),'rank=\"2\"/>') SECOND_ATTRIBUTE,"," RTRIM(TRIM(SUBSTR(OUTPRED.\"Attribute3\",17,100)),'rank=\"3\"/>') THIRD_ATTRIBUTE","FROM (SELECT CUST_ID,"," PREDICTION(GLM_CLASS_MODEL USING *) PREDICTION_AFFINITY_CARD,"," PREDICTION_DETAILS(GLM_CLASS_MODEL USING *) PD"," FROM TEST_DATA_CLAS"," ORDER BY CUST_ID) OUT,"," XMLTABLE('/Details'"," PASSING OUT.PD"," COLUMNS "," \"Attribute1\" XMLType PATH 'Attribute[1]',"," \"Attribute2\" XMLType PATH 'Attribute[2]',"," \"Attribute3\" XMLType PATH 'Attribute[3]') "," OUTPRED","FETCH FIRST 10 ROWS ONLY;"],"enabled":true,"result":{"startTime":1715224832609,"interpreter":"sql.medium","endTime":1715224832800,"results":[{"message":"CUST_ID\tPRED_AFFINITY_CARD\tFIRST_ATTRIBUTE\tSECOND_ATTRIBUTE\tTHIRD_ATTRIBUTE\n100003\t0\t\"HOUSEHOLD_SIZE\" actualValue=\"2\" weight=\".379\" \t\"OCCUPATION\" actualValue=\"Sales\" weight=\".045\" \t\"FLAT_PANEL_MONITOR\" actualValue=\"1\" weight=\".011\" \n100006\t0\t\"HOUSEHOLD_SIZE\" actualValue=\"9+\" weight=\".216\" \t\"Y_BOX_GAMES\" actualValue=\"1\" weight=\".028\" \t\"YRS_RESIDENCE\" actualValue=\"2\" weight=\".011\" \n100011\t0\t\"HOUSEHOLD_SIZE\" actualValue=\"2\" weight=\".055\" \t\"OCCUPATION\" actualValue=\"Farming\" weight=\".018\" \t\"BOOKKEEPING_APPLICATION\" actualValue=\"0\" weight=\".011\" \n100016\t0\t\"HOUSEHOLD_SIZE\" actualValue=\"9+\" weight=\".357\" \t\"FLAT_PANEL_MONITOR\" actualValue=\"1\" weight=\".008\" \t\"HOME_THEATER_PACKAGE\" actualValue=\"0\" weight=\".006\" \n100023\t1\t\"YRS_RESIDENCE\" actualValue=\"8\" weight=\".155\" \t\"Y_BOX_GAMES\" actualValue=\"0\" weight=\".076\" \t\"BOOKKEEPING_APPLICATION\" actualValue=\"1\" weight=\".029\" \n100032\t0\t\"HOUSEHOLD_SIZE\" actualValue=\"2\" weight=\".304\" \t\"OCCUPATION\" actualValue=\"Cleric.\" weight=\".029\" \t\"FLAT_PANEL_MONITOR\" actualValue=\"1\" weight=\".007\" \n100041\t0\t\"HOUSEHOLD_SIZE\" actualValue=\"1\" weight=\".047\" \t\"OCCUPATION\" actualValue=\"Handler\" weight=\".015\" \t\"Y_BOX_GAMES\" actualValue=\"1\" weight=\".005\" \n100047\t0\t\"HOUSEHOLD_SIZE\" actualValue=\"1\" weight=\".108\" \t\"Y_BOX_GAMES\" actualValue=\"1\" weight=\".013\" \t\"OCCUPATION\" actualValue=\"Cleric.\" weight=\".007\" \n100050\t0\t\"HOUSEHOLD_SIZE\" actualValue=\"9+\" weight=\".027\" \t\"BOOKKEEPING_APPLICATION\" actualValue=\"0\" weight=\".004\" \t\"Y_BOX_GAMES\" actualValue=\"1\" weight=\".003\" \n100051\t0\t\"HOUSEHOLD_SIZE\" actualValue=\"2\" weight=\".213\" \t\"OCCUPATION\" actualValue=\"Handler\" weight=\".085\" \t\"FLAT_PANEL_MONITOR\" actualValue=\"1\" weight=\".005\" \n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"html","title":null,"message":["%md","## Create Classification Model using Settings Table","","The settings table is an alternative way to specify algorithm settings to build the model. We first drop the settings table, create it, and then populate it with settings before building the model."],"enabled":true,"result":{"startTime":1715224832879,"interpreter":"md.medium","endTime":1715224832945,"results":[{"message":"<h2 id=\"create-classification-model-using-settings-table\">Create Classification Model using Settings Table<\/h2>\n<p>The settings table is an alternative way to specify algorithm settings to build the model. We first drop the settings table, create it, and then populate it with settings before building the model.<\/p>\n","type":"HTML"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":true,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":false,"hideVizConfig":true,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"raw","title":"Drop settings and diagnostics table and create the GLM model Settings table","message":["%script ","","-- Drop any previous GLM Model Settings table for notebook repeatability","BEGIN EXECUTE IMMEDIATE 'DROP TABLE GLMC_SETTINGS';","EXCEPTION WHEN OTHERS THEN NULL; END;","/","-- Drop any previous Model Diagnostics table for notebook repeatability","BEGIN EXECUTE IMMEDIATE 'DROP TABLE GLMC_DIAG';","EXCEPTION WHEN OTHERS THEN NULL; END;","/","--Create Model Settings Table","CREATE TABLE GLMC_SETTINGS ("," SETTING_NAME VARCHAR2(30),"," SETTING_VALUE VARCHAR2(4000));"],"enabled":true,"result":{"startTime":1715224833026,"interpreter":"script.medium","endTime":1715224833176,"results":[{"message":"\nPL/SQL procedure successfully completed.\n\n\n---------------------------\n\nPL/SQL procedure successfully completed.\n\n\n---------------------------\n\nTable GLMC_SETTINGS created.\n\n\n---------------------------\n","type":"TEXT"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":6,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"raw","title":"Define model settings for automated data preparation and feature selection","message":["%script ","","BEGIN "," INSERT INTO GLMC_SETTINGS (SETTING_NAME, SETTING_VALUE) VALUES (DBMS_DATA_MINING.ALGO_NAME, DBMS_DATA_MINING.ALGO_GENERALIZED_LINEAR_MODEL);"," INSERT INTO GLMC_SETTINGS (SETTING_NAME, SETTING_VALUE) VALUES (DBMS_DATA_MINING.PREP_AUTO, DBMS_DATA_MINING.PREP_AUTO_ON); ","","-- output row diagnostic statistics into a table named GLMC_SH_SAMPLE_DIAG "," INSERT INTO GLMC_SETTINGS (SETTING_NAME, SETTING_VALUE) VALUES (DBMS_DATA_MINING.GLMS_DIAGNOSTICS_TABLE_NAME, 'GLMC_SH_SAMPLE_DIAG'); "," ","-- turn ridge regression on or off. By default the system turns it on if there is a multicollinearity. Ridge regression cannot be used with feature selection and feature generation, so we will keep it off in this example"," INSERT INTO GLMC_SETTINGS (SETTING_NAME, SETTING_VALUE) VALUES (DBMS_DATA_MINING.GLMS_RIDGE_REGRESSION, 'GLMS_RIDGE_REG_DISABLE'); "," ","-- turn on feature selection. By default the system does not do feature selection. Some options for feature selection are show below too"," INSERT INTO GLMC_SETTINGS (SETTING_NAME, SETTING_VALUE) VALUES (DBMS_DATA_MINING.GLMS_FTR_SELECTION, DBMS_DATA_MINING.GLMS_FTR_SELECTION_ENABLE);"," INSERT INTO GLMC_SETTINGS (SETTING_NAME, SETTING_VALUE) VALUES (DBMS_DATA_MINING.GLMS_MAX_FEATURES, '30');"," INSERT INTO GLMC_SETTINGS (SETTING_NAME, SETTING_VALUE) VALUES (DBMS_DATA_MINING.GLMS_PRUNE_MODEL, DBMS_DATA_MINING.GLMS_PRUNE_MODEL_ENABLE); "," ","-- turn on feature generation. We will do Quadratic features, but Cubic is also available. Feature generation requires feature selection to be enabled."," INSERT INTO GLMC_SETTINGS (SETTING_NAME, SETTING_VALUE) VALUES (DBMS_DATA_MINING.GLMS_FTR_GENERATION, DBMS_DATA_MINING.GLMS_FTR_GENERATION_ENABLE);"," INSERT INTO GLMC_SETTINGS (SETTING_NAME, SETTING_VALUE) VALUES (DBMS_DATA_MINING.GLMS_FTR_GEN_METHOD, DBMS_DATA_MINING.GLMS_FTR_GEN_QUADRATIC);","-- INSERT INTO GLMC_SETTINGS (SETTING_NAME, SETTING_VALUE) VALUES (DBMS_DATA_MINING.GLMS_FTR_GEN_METHOD, DBMS_DATA_MINING.GLMS_FTR_GEN_CUBIC);"," ","END;"],"enabled":true,"result":{"startTime":1715224833261,"interpreter":"script.medium","endTime":1715224833391,"results":[{"message":"\nPL/SQL procedure successfully completed.\n\n","type":"TEXT"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"raw","title":"Build classification model","message":["%script","","BEGIN DBMS_DATA_MINING.DROP_MODEL('GLMC_SH_REGR_SAMPLE');","EXCEPTION WHEN OTHERS THEN NULL; END;","/","","DECLARE"," V_XLST DBMS_DATA_MINING_TRANSFORM.TRANSFORM_LIST;"," ","BEGIN"," DBMS_DATA_MINING.CREATE_MODEL("," MODEL_NAME => 'GLMC_SH_REGR_SAMPLE',"," MINING_FUNCTION => 'CLASSIFICATION',"," DATA_TABLE_NAME => 'TRAIN_DATA_CLAS',"," CASE_ID_COLUMN_NAME => 'CUST_ID',"," TARGET_COLUMN_NAME => 'AFFINITY_CARD',"," SETTINGS_TABLE_NAME => 'GLMC_SETTINGS',"," XFORM_LIST => V_XLST);",""," DBMS_OUTPUT.PUT_LINE ('Created model: GLMC_SH_REGR_SAMPLE ');","END;"],"enabled":true,"result":{"startTime":1715224833494,"interpreter":"script.medium","endTime":1715224835993,"results":[{"message":"\nPL/SQL procedure successfully completed.\n\n\n---------------------------\nCreated model: GLMC_SH_REGR_SAMPLE \n\n\nPL/SQL procedure successfully completed.\n\n","type":"TEXT"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"html","title":null,"message":["%md","","## End of Script"],"enabled":true,"result":{"startTime":1715224836069,"interpreter":"md.medium","endTime":1715224836130,"results":[{"message":"<h2 id=\"end-of-script\">End of Script<\/h2>\n","type":"HTML"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":true,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":false,"hideVizConfig":true,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"html","title":null,"message":["%md"],"enabled":true,"result":{"startTime":1715224836215,"interpreter":"md.medium","endTime":1715224836288,"results":[],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":true,"width":12,"hideResult":true,"dynamicFormParams":null,"row":0,"hasTitle":false,"hideVizConfig":true,"hideGutter":true,"relations":[],"forms":"[]"}],"version":"6","snapshot":false,"tags":null}]
